const router = require('koa-router')()
const DB = require('../other/DB');
const uuid = require('uuid').v1;


router.prefix('/api/statistics');
// 暂存代码
router.get('/', async ctx => {
    ctx.body = await DB.query(`SELECT
	nickname,
	title,
	i.total_points total,
	i.inspectA A,
	i.inspectB B,
	i.inspectC C,
	i.inspectD D,
	i.inspectE E,
	i.id id,
	x.id tid,
	x.create_time,
	x.description
FROM
	(SELECT
		u.username,
		u.nickname,
		t.id id,
		t.title,
		t.create_time,
		t.description
	FROM
		\`user\` u,
		topic t 
	WHERE
		u.permission = 'user' 
		AND u.desable = 0 
	) x
LEFT JOIN inspect i ON x.id = i.topic_id 
AND x.username = i.user_id 
ORDER BY x.create_time`);
});


router.post('/topic', async ctx => {
    ctx.body = await DB.query(`SELECT 
    code_content \`Code\`,
    image_path,
    title
FROM topic WHERE id = ?`, [ctx.request.body.id])
});

router.post('/inspect', async ctx => {
    ctx.body = await DB.query(`SELECT
	i.code_content \`Code\`,
	t.image_path,
	u.nickname,
	t.title
FROM
	topic t,
	inspect i,
	\`user\` u
WHERE
	i.id = ?
	AND t.id = i.topic_id 
	AND u.username = i.user_id`, [ctx.request.body.id])
});



module.exports = router